package com.vic.Builder;


import com.vic.Utils.JsonUtils;
import com.vic.Utils.StringUtils;
import com.vic.Utils.dbutils;
import com.vic.bean.constant;
import com.vic.bean.fieldInfo;
import com.vic.bean.tableInfo;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import java.sql.*;
import java.util.*;

/**
 * @Author: Vic Ly
 */
public class builderTable {
    private static final Logger log = LoggerFactory.getLogger(builderTable.class);
    private static Connection conn = null;
    private static String TABLE_NAME_SQL = "show table status ";
    private static String FIELD_NAME_SQL = "show full columns from %s";
    private static String INDEX_SQL = "show index from %s";
    static {
        String driver = dbutils.getString("db.driver");
        String url = dbutils.getString("db.url");
        String username = dbutils.getString("db.username");
        String password = dbutils.getString("db.password");
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            log.error("数据库连接失败",e);
        }
    }
    // 读取表中数据
    public static List<tableInfo> getTables() {
        PreparedStatement ps = null;
        ResultSet tableResultSet = null;
        List<tableInfo> tableInfos = new ArrayList<tableInfo>();
        try {
            ps = conn.prepareStatement(TABLE_NAME_SQL);
            tableResultSet = ps.executeQuery();
            while (tableResultSet.next()){
                String tableName = tableResultSet.getString("Name");
                String comment = tableResultSet.getString("Comment");
                //log.info("tableName:{},comment:{}",tableName,comment);

                String beanName = tableName;
                if(constant.IGNORE_TABLE_PREFIX){
                    beanName = tableName.substring(beanName.indexOf("_")+1);
                }
                // 期望结果 beanName:GoodsInfo
                beanName = processFiled(beanName,true);
                //log.info("beanName:{}",beanName);
                tableInfo tableInfo = new tableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + constant.SUFFIX_BEAN_QUERY);
                tableInfos.add(tableInfo);
                /*
                log.info("tableName:{},beanName:{},comment:{},BeanParamName:{}",tableInfo.getTableName(),
                        tableInfo.getBeanName(),tableInfo.getComment(),tableInfo.getBeanParamName());
                 */
                processFieldInfo(tableInfo);

                 processIndex(tableInfo);
                log.info("tableInfo:{}",JsonUtils.convertObjToJson(tableInfo));
                //log.info("表:{}", JsonUtils.convertObjToJson(tableInfos));
                //log.info("字段:{}",JsonUtils.convertObjToJson(fieldInfoList));
                tableInfos.add(tableInfo);
            }
        } catch (SQLException e) {
            log.error("sql执行失败",e);
        }finally {
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(tableResultSet != null){
                try {
                    tableResultSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return tableInfos;
    }

    // 将表名形如abc_name的解析为AbcName 转换为驼峰
    public static String processFiled(String field,Boolean upperCaseFirstLetter){
        StringBuilder sb = new StringBuilder();
        String[] fields = field.split("_");
        sb.append(upperCaseFirstLetter ? StringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i = 1,len = fields.length; i < len; i++) {
            sb.append(StringUtils.upperCaseFirstLetter(fields[i]));
        }
        return sb.toString();
    }

    // 解析字段名
    public static void processFieldInfo(tableInfo tableInfo){
        PreparedStatement stat = null;
        ResultSet fieldSet = null;
        // 基本字段信息
        List<fieldInfo> fieldInfos = new ArrayList<fieldInfo>();
        // 扩展字段信息
        List<fieldInfo> fieldExtendList = new ArrayList<fieldInfo>();
        try {
            stat = conn.prepareStatement(String.format(FIELD_NAME_SQL,tableInfo.getTableName()));
            fieldSet = stat.executeQuery();
            Boolean haveDate =false;
            Boolean haveDateTime =false;
            Boolean haveBigDecimal =false;
            while (fieldSet.next()){
                String Field = fieldSet.getString("Field"); // 数据库中原始名
                String Type = fieldSet.getString("Type");
                String Extra = fieldSet.getString("Extra");
                String Comment = fieldSet.getString("Comment");
                // 字段名驼峰处理(处理原始名)
               String  PropertyName = processFiled(Field,false);
                // 去掉类型后面的长度约束  varchar(50) --> varchar
                if(Type.indexOf("(") > 0){
                    Type = Type.substring(0,Type.indexOf("("));
                }
               fieldInfo fieldInfo = new fieldInfo();
                fieldInfo.setName(Field);
                fieldInfo.setComment(Comment);
                fieldInfo.setSqlType(Type);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(Extra) ? true : false);
                fieldInfo.setPropertyName(PropertyName);
                fieldInfo.setJavaType(processJavaType(Type));
                fieldInfo.setSqlType(Type);
                fieldInfos.add(fieldInfo);
                //log.info("Field:{},Type:{},Extra:{},Comment:{}",fieldInfo.getPropertyName(),fieldInfo.getJavaType(),fieldInfo.getAutoIncrement(),fieldInfo.getComment());
                //log.info("JavaType:{}",fieldInfo.getJavaType());
                if(ArrayUtils.contains(constant.SQL_DATE,Type)){
                    haveDate = true;
                }
                if(ArrayUtils.contains(constant.SQL_DATE_TIME,Type)){
                    haveDateTime = true;
                }
                if(ArrayUtils.contains(constant.SQL_DECIMAL,Type)){
                    haveBigDecimal = true;
                }

                if(ArrayUtils.contains(constant.SQL_STRING,Type)){
                    fieldInfo fuzzyField = new fieldInfo();
                    fuzzyField.setJavaType(fieldInfo.getJavaType());
                    fuzzyField.setPropertyName(PropertyName + constant.SUFFIX_BEAN_QUERY_FUZZY);
                    fuzzyField.setName(fieldInfo.getName());
                    fuzzyField.setSqlType(Type);
                    fieldExtendList.add(fuzzyField);
                }
                if(ArrayUtils.contains(constant.SQL_DATE_TIME,Type)||ArrayUtils.contains(constant.SQL_DATE,Type)) {
                    String propertyNameStart = PropertyName + constant.SUFFIX_BEAN_QUERY_TIME_START;
                    String propertyNameEnd = PropertyName + constant.SUFFIX_BEAN_QUERY_TIME_END;

                    fieldInfo timeStartField = new fieldInfo();
                    timeStartField.setJavaType("String");
                    timeStartField.setPropertyName(propertyNameStart);
                    timeStartField.setName(fieldInfo.getName());
                    timeStartField.setSqlType(Type);
                    fieldExtendList.add(timeStartField);

                    fieldInfo timeEndField = new fieldInfo();
                    timeEndField.setJavaType("String");
                    timeEndField.setPropertyName(propertyNameEnd);
                    timeEndField.setName(fieldInfo.getName());
                    timeEndField.setSqlType(Type);
                    fieldExtendList.add(timeEndField);
                }

                tableInfo.setHaveDate(haveDate);
                tableInfo.setHaveDateTime(haveDateTime);
                tableInfo.setHaveBigDecimal(haveBigDecimal);
                tableInfo.setFieldExtendList(fieldExtendList);
            }
        } catch (SQLException e) {
            log.error("sql执行失败",e);
        }finally {
            if(stat != null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(fieldSet != null){
                try {
                    fieldSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        tableInfo.setFieldList(fieldInfos);
    }

    // 解析唯一索引
    // mysql中默认情况下1表示普通索引，0表示唯一索引
    // mysql中0表示不能重复的索引,1表示可以重复的索引
    public static List<fieldInfo> processIndex(tableInfo tableInfo){
        PreparedStatement stat = null;
        ResultSet indexSet = null;
        List<fieldInfo> fieldInfos = new ArrayList<fieldInfo>();
        try {
            stat = conn.prepareStatement(String.format(INDEX_SQL,tableInfo.getTableName()));
            indexSet = stat.executeQuery();
            while (indexSet.next()){
                String keyName = indexSet.getString("key_name");
                int nonUnique = indexSet.getInt("non_unique");
                String columnName = indexSet.getString("column_name");
                if(nonUnique == 1) continue; // 值为1的索引直接跳过
                List<fieldInfo> keyFieldInfos = tableInfo.getKeyIndexMap().get("keyName");
                if(null == keyFieldInfos){
                    // 判读是否存在一个keyFieldInfos对象,没有就判断
                    keyFieldInfos = new ArrayList<fieldInfo>();
                    tableInfo.getKeyIndexMap().put(keyName,keyFieldInfos);
                }
                // 每当出现一个索引，都需要将整个列表遍历一遍，当表多的时候就非常小航性能
                /*for(fieldInfo fieldInfo:tableInfo.getFieldList()){
                    if(fieldInfo.getName().equals(columnName)){
                        keyFieldInfos.add(fieldInfo);
                    }
                }*/
                // 优化如下(这样更改后,则只循环未添加,已经加过的就不用循环了)
                Map<String,fieldInfo> tempMap = new HashMap();
                for(fieldInfo fieldInfo:tableInfo.getFieldList()){
                    tempMap.put(fieldInfo.getName(),fieldInfo);
                }
                keyFieldInfos.add(tempMap.get(columnName));
            }
        } catch (SQLException e) {
            log.error("读取索引失败",e);
        } finally {
            if( stat != null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if( indexSet != null){
                try {
                    indexSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return null;
    }

    // 类型转换 Type -> JavaType
    private static String processJavaType(String type){
        if(ArrayUtils.contains(constant.SQL_INTEGER,type)){
            return "Integer";
        } else if (ArrayUtils.contains(constant.SQL_LONG,type)) {
            return "Long";
        } else if (ArrayUtils.contains(constant.SQL_STRING,type)) {
            return "String";
        } else if (ArrayUtils.contains(constant.SQL_DATE,type)||ArrayUtils.contains(constant.SQL_DATE_TIME,type)) {
            return "Date";
        } else if (ArrayUtils.contains(constant.SQL_DECIMAL,type)) {
            // BigDecimal 约等于 Double + float
            return "Double";
        } else{
            throw new RuntimeException("该类型无法识别:  "+type);
        }
    }
}
